IF OBJECT_ID ('place_delete','TR') IS NOT NULL
   DROP TRIGGER place_delete;
GO
CREATE TRIGGER place_delete ON PLACE_OF_REST
INSTEAD OF DELETE AS
BEGIN
	--BEGIN TRY 
	DELETE FROM SEA_PLACE
		WHERE (place_id IN (SELECT id 
								FROM DELETED))
	DELETE FROM HOTEL_PLACE
		WHERE (place_id IN (SELECT id 
								FROM DELETED))
	DELETE FROM COUNTRY_PLACE
		WHERE (place_id IN (SELECT id 
								FROM DELETED))
	CREATE TABLE tmp_r ([id] [int], [cnt] [int])
	INSERT INTO tmp_r SELECT tour_id, COUNT(PLACE_TOUR.place_id) - ISNULL((SELECT COUNT(*)
															FROM TOUR_ROUTE
																JOIN PLACE_TOUR ON (id = TOUR_ROUTE.place_id)
															WHERE (PLACE_TOUR.place_id IN (SELECT id 
																								FROM DELETED))
															AND (tour_id = X.tour_id)
															GROUP BY tour_id), 0)
		FROM TOUR_ROUTE X
			JOIN PLACE_TOUR ON (id = X.place_id)
		GROUP BY tour_id
	UPDATE [GROUP] SET
		tour_id = NULL
		WHERE (tour_id IN (SELECT id
								FROM tmp_r
							WHERE (cnt = 0)))
	DELETE FROM TOUR_ROUTE
		WHERE (place_id IN (SELECT id
								FROM PLACE_TOUR
								WHERE (place_id IN (SELECT id 
														FROM DELETED))
						    ))
	DELETE FROM TOUR
		WHERE (id IN (SELECT id
						FROM tmp_r
						WHERE (cnt =0)))	
	DELETE FROM PLACE_TIME 
		WHERE (place_id IN (SELECT id
								FROM PLACE_TOUR
								WHERE (place_id IN (SELECT id 
														FROM DELETED))
							)) AND (start_date > CURRENT_TIMESTAMP)
	UPDATE PLACE_TOUR SET 
		place_id = NULL
		WHERE (place_id IN (SELECT id 
								FROM DELETED))
	DELETE FROM PLACE_OF_REST
		WHERE id IN (SELECT id 
						FROM DELETED)
	DROP TABLE tmp_r
--	END TRY
--	BEGIN CATCH
--		PRINT 'PLACE_OF_REST : ERROR ON DELETE'
--		ROLLBACK
--	END CATCH
END